"""
1、读取所有的sql文件
2、循环解析sql文件
3、去除字段名，表名，主键，类型
"""

import os
import re


def read_template_file(path):
    with open(path, mode="r", encoding="UTF-8") as file:
        return "".join(file.readlines())


def write_template_file(template, path):
    with open(path, mode="w", encoding="UTF-8",newline="\n") as file:
        file.write(template)


mapping = {
    "int": "INT",
    "varchar": "STRING",
    "decimal": "DOUBLE",
    "datetime": "STRING",
    "tinytext": "STRING",
    "bigint": "BIGINT"
}

table_time_field = {
    "t_commodity": "update_time",
    "t_commodity_cate": "update_time",
    "t_coupon": "update_time",
    "t_coupon_member": "update_time",
    "t_coupon_order": "create_time",
    "t_member": "update_time",
    "t_member_addr": "update_time",
    "t_order": "update_time",
    "t_order_commodity": "update_time"
}


def mysql_type_to_hive_type(mysql_field_types):
    hive_field_types = []

    for mysql_type in mysql_field_types:
        # 正在匹配去除类型后面二点长度
        search = re.search("\(.*\)", mysql_type)
        if search is not None:
            mysql_type = mysql_type.replace(search.group(0), "")

        # 通过mysql类型获取hive类型
        if mysql_type in mapping:
            hive_field_types.append(mapping[mysql_type])
        else:
            hive_field_types.append("STRING")

    return hive_field_types


if __name__ == '__main__':
    # 1、获取所有sql文件名
    file_list = os.listdir("../mysql_ddl")

    # 2、循环读取
    for sql_file in file_list:
        # 读取文件
        with open(f"../mysql_ddl/{sql_file}", encoding="UTF-8") as file:
            # 读取sql语句，拼接成一个字符串
            mysql_ddl_str = "".join(file.readlines())

        # 使用正则表达式解析数据
        # re.S : 多行模式
        field_str = re.search("\((.*)PRIMARY", mysql_ddl_str, re.S).group(1).replace("`", "")

        # 解析取出字段名
        field_names = [s.strip().split(" ")[0] for s in field_str.split(",\n")[:-1]]

        # mysql建表语句的字段类型
        mysql_field_types = [s.strip().split(" ")[1] for s in field_str.split(",\n")[:-1]]

        # 取出建表语句的主键
        pk = re.search("PRIMARY KEY \((.*?)\)", mysql_ddl_str, re.S).group(1).replace("`", "")

        # 表名
        mysql_table_name = re.search("`(.*?)`", mysql_ddl_str, re.S).group(1)

        # 构建hive表明
        hive_table_name = f"ods_{mysql_table_name}"

        hive_table_name_delta = f"ods_{mysql_table_name}_delta"

        # 将mysql类型转换成hive类型
        hive_field_types = mysql_type_to_hive_type(mysql_field_types)

        #################################全量数据采集json生成###########################################
        # 1、读取模板文件
        datax_template = read_template_file("template/datax_template.json")

        # 构建datax字段名和类型
        hive_field_name_and_type = ",".join(
            ['{"name": "%s","type": "%s"}' % (field_name, field_type) for (field_name, field_type) in
             zip(field_names, hive_field_types)])

        # 替换mysql字段列表
        datax_template = datax_template \
            .replace("{fields}", ",".join([f'"{name}"' for name in field_names])) \
            .replace("{mysql_table_name}", mysql_table_name) \
            .replace("{hive_table_name}", hive_table_name) \
            .replace("{hive_field_name_and_type}", hive_field_name_and_type)

        # 保存模板
        write_template_file(datax_template, f"../datax/{hive_table_name}.json")

        #################################全量数据采集shell脚本生成###########################################
        # 1、读取模板文件
        datax_template = read_template_file("template/sh_template.sh")

        # 替换mysql字段列表
        datax_template = datax_template \
            .replace("{hive_table_name}", hive_table_name)

        # 保存模板
        write_template_file(datax_template, f"../bin/{hive_table_name}.sh")


        #################################全量表的建表语句###########################################
        # 1、读取模板文件
        datax_template = read_template_file("template/ddl_template.sql")

        # 构建datax字段名和类型
        field_name_and_type = ",\n".join(
            ['%s %s' % (field_name, field_type) for (field_name, field_type) in
             zip(field_names, hive_field_types)])

        # 替换mysql字段列表
        datax_template = datax_template \
            .replace("{hive_table_name}", hive_table_name)\
            .replace("{field_name_and_type}", field_name_and_type)\

        # 保存模板
        write_template_file(datax_template, f"../ddl/{hive_table_name}.sql")

        # 判断是否需要做增量
        if mysql_table_name in table_time_field:
            #################################增量数据采集json生成###########################################
            # 1、读取模板文件
            datax_template = read_template_file("template/datax_template_delta.json")

            # 构建datax字段名和类型
            hive_field_name_and_type = ",".join(
                ['{"name": "%s","type": "%s"}' % (field_name, field_type) for (field_name, field_type) in
                 zip(field_names, hive_field_types)])

            # 获取时间字段
            time_field = table_time_field[mysql_table_name]

            # 替换mysql字段列表
            datax_template = datax_template \
                .replace("{fields}", ",".join([f'"{name}"' for name in field_names])) \
                .replace("{mysql_table_name}", mysql_table_name) \
                .replace("{hive_table_name}", hive_table_name_delta) \
                .replace("{hive_field_name_and_type}", hive_field_name_and_type) \
                .replace("{time_field}", time_field)

            # 保存模板
            write_template_file(datax_template, f"../datax_delta/{hive_table_name_delta}.json")

            #################################增量数据采集shell脚本生成###########################################
            # 1、读取模板文件
            datax_template = read_template_file("template/sh_template_delta.sh")

            # 替换mysql字段列表
            datax_template = datax_template \
                .replace("{hive_table_name}", hive_table_name) \
                .replace("{hive_table_name_delta}", hive_table_name_delta)

            # 保存模板
            write_template_file(datax_template, f"../bin_delta/{hive_table_name_delta}.sh")

            #################################增量合并数据的sql###########################################

            # 1、读取模板文件
            datax_template = read_template_file("template/merge_template.sql")
            time_field = table_time_field[mysql_table_name]

            # 替换mysql字段列表
            datax_template = datax_template \
                .replace("{hive_table_name}", hive_table_name) \
                .replace("{hive_table_name_delta}", hive_table_name_delta) \
                .replace("{fields}", ",".join(field_names)) \
                .replace("{pk}", pk) \
                .replace("{time_field}", time_field)

            # 保存模板
            write_template_file(datax_template, f"../dql/merge_{hive_table_name}.sql")


            #################################增量表的建表语句###########################################
            # 1、读取模板文件
            datax_template = read_template_file("template/ddl_template_delta.sql")

            # 构建datax字段名和类型
            field_name_and_type = ",\n".join(
                ['%s %s' % (field_name, field_type) for (field_name, field_type) in
                 zip(field_names, hive_field_types)])

            # 替换mysql字段列表
            datax_template = datax_template \
                .replace("{hive_table_name_delta}", hive_table_name_delta) \
                .replace("{field_name_and_type}", field_name_and_type) \
     \
            # 保存模板
            write_template_file(datax_template, f"../ddl_delta/{hive_table_name_delta}.sql")
